package shop.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import shop.bean.OfferBean;
import shop.bean.UserBean;
import shop.util.ConnectionManager;

public class UserDAO 	
{
	static Connection currentCon = null;
	static ResultSet rs = null;  



	public static UserBean login(UserBean bean) {

		//preparing some objects for connection 
		Statement stmt = null;    

		String username = bean.getUsername();    
		String password = bean.getPassword();   

		String searchQuery =
				"select U.*, UR.role_name " +
				"from users AS U LEFT JOIN user_roles AS UR ON UR.user_name=U.user_name " +
				"where U.user_name='"	+ username	+ 
					"' AND U.user_pass='"+ password+ 
					"'";

		// "System.out.println" prints in the console; Normally used to trace the process
		System.out.println("Your user name is " + username);          
		System.out.println("Your password is " + password);
		System.out.println("Query: "+searchQuery);

		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        
			boolean more = rs.next();

			// if user does not exist set the isValid variable to false
			if (!more) 
			{
				System.out.println("Sorry, you are not a registered user! Please sign up first");
				bean.setValid(false);
			} 

			//if user exists set the isValid variable to true
			else if (more) 
			{
				bean.setRole(rs.getString("role_name"));
				bean.setName(rs.getString("name"));
				bean.setUserName(rs.getString("user_name"));
				bean.setCity(rs.getString("city"));
				bean.setZipcode(rs.getString("zip_code"));
				bean.setCountry(rs.getString("country"));
				bean.setPassword(rs.getString("user_pass"));
				bean.setStreetAddress(rs.getString("street_address"));
				bean.setValid(true);

				System.out.println("Welcome " + bean.getName());
			}
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}

		return bean;

	}	
	
	
	

	public static void newUser(UserBean o){

		//preparing some objects for connection 
		Statement stmt = null; 

		String query = "INSERT INTO users (name, user_name, user_pass, country, city, street_address, zip_code) VALUES  " +
				" ('"+ o.getName() +"', '"+ o.getUsername() +"', '"+ o.getPassword() +"', '"+ o.getCountry() +"', '"+ o.getCity() +"', " +
				"'"+ o.getStreetAddress() +"', '"+ o.getZipcode() +"')";

		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.execute(query);	   
		} 

		catch (Exception ex) 
		{
			System.out.println("New user failed: An Exception has occurred! " + ex);
			System.out.println(query);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}
	
	public static void saveUser(UserBean o){

		//preparing some objects for connection 
		Statement stmt = null; 

		String query = "UPDATE users SET " +
				" name='"+ o.getName() +"', user_pass='"+ o.getPassword() +"', country='"+ o.getCountry() +"', city='"+ o.getCity() +"', " +
				"street_address='"+ o.getStreetAddress() +"', zip_code='"+ o.getZipcode() +"'";

		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.execute(query);
			System.out.println(query);	   
		} 

		catch (Exception ex) 
		{
			System.out.println("Save user failed: An Exception has occurred! " + ex);
			System.out.println(query);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}
}